package com.shsh.safemanage.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.TreeMap;

import com.shsh.safemanage.model.AbstractAccident;
import com.shsh.safemanage.model.AccidentCasualty;
import com.shsh.safemanage.model.AccidentCheckForm;
import com.shsh.safemanage.model.AccidentFacade;
import com.shsh.safemanage.model.AccidentInfoSheet;
import com.shsh.safemanage.model.AccidentQuickReport;
import com.shsh.safemanage.model.AccidentRegisterForm;
import com.shsh.safemanage.model.AccidentReport;
import com.shsh.safemanage.model.AccidentType;
import com.shsh.safemanage.model.Problem;
import com.shsh.safemanage.util.Constants;
import com.shsh.safemanage.util.Util;
import com.shsh.safemanage.util.ValueUtil;

public class AccidentDao {
	public List<AccidentFacade> searchAccidentByFacadeID(Long theFacadeID) {
		return searchAccident(theFacadeID, null, null, null, null, null, null, null, null);
	}

	public List<AccidentFacade> searchAccidentByWriteUserDepartement1(Long writeDepartment1ID) {
		return searchAccident(null, null, null, null, null, null, null, writeDepartment1ID, null);
	}

	/**
	 * 
	 * 
	 * @param facadeID
	 * @param department1ID
	 * @param equipmentID
	 * @param accidentTypeID
	 * @param problemTitle
	 * @param dateStart
	 * @param dateEnd
	 * @param writeDepartment1ID
	 * @return
	 */
	public List<AccidentFacade> searchAccident(Long theFacadeID, Long department1ID, Long equipmentID, Long accidentTypeID,
			String problemTitle, String dateStart, String dateEnd, Long writeDepartment1ID, Integer accidentStatus) {
		String conditionSQL = "";
		boolean hasPre = true;

		if (accidentStatus != null && accidentStatus > 0) {
			if (hasPre) {
				conditionSQL += " and ";
			}
			conditionSQL += "ac.ACCIDENT_STATUS=" + accidentStatus + " ";
			hasPre = true;
		}

		if (theFacadeID != null && theFacadeID > 0) {
			if (hasPre) {
				conditionSQL += " and ";
			}
			conditionSQL += "ac.FACADE_ID=" + theFacadeID + " ";
			hasPre = true;
		}

		if (department1ID != null && department1ID > 0) {
			if (hasPre) {
				conditionSQL += " and ";
			}
			conditionSQL += "p.DEPARTMENT1_ID=" + department1ID + " ";
			hasPre = true;
		}

		if (writeDepartment1ID != null && writeDepartment1ID > 0) {
			if (hasPre) {
				conditionSQL += " and ";
			}
			conditionSQL += "p.WRITE_USER_DEPARTMENT1_ID=" + writeDepartment1ID + " ";
			hasPre = true;
		}

		if (equipmentID != null && equipmentID > 0) {
			if (hasPre) {
				conditionSQL += "and ";
			}
			conditionSQL += "p.EQUIPMENT_ID=" + equipmentID + " ";
			hasPre = true;
		}

		if (accidentTypeID != null && accidentTypeID > 0) {
			if (hasPre) {
				conditionSQL += "and ";
			}
			conditionSQL += "ac.ACCIDENT_TYPE_ID=" + accidentTypeID + " ";
			hasPre = true;
		}

		if (problemTitle != null && problemTitle.trim().length() > 0) {
			if (hasPre) {
				conditionSQL += "and ";
			}
			conditionSQL += "ac.ACCIDENT_NAME like '%" + problemTitle.trim() + "%' ";
			hasPre = true;
		}

		if (dateStart != null && dateStart.trim().length() > 0) {
			if (hasPre) {
				conditionSQL += "and ";
			}
			conditionSQL += "ac.ACCIDENT_WRITE_TIME>='" + dateStart + " 00:00:00' ";
			hasPre = true;
		}

		if (dateEnd != null && dateEnd.trim().length() > 0) {
			if (hasPre) {
				conditionSQL += "and ";
			}
			conditionSQL += "ac.ACCIDENT_WRITE_TIME<='" + dateEnd + " 23:59:59' ";
			hasPre = true;
		}

		String sql = " select ACCIDENT_WRITE_USER_ID, ACCIDENT_TIME, PROBLEM_TYPE, fa.ID, FACADE_ID, ACCIDENT_TYPE_ID, ACCIDENT_NAME, ACCIDENT_STATUS, at.ACCIDENT_TYPE from ("
				+ " select ACCIDENT_WRITE_USER_ID, ACCIDENT_TIME, PROBLEM_TYPE, ac.ID, FACADE_ID, ac.ACCIDENT_TYPE_ID, ac.ACCIDENT_NAME, ac.ACCIDENT_STATUS from accident_registerform as ac inner join problem as p on p.ID = ac.ID "
				+ conditionSQL
				+ " union "
				+ " select ACCIDENT_WRITE_USER_ID, ACCIDENT_TIME, PROBLEM_TYPE, ac.ID, FACADE_ID, ac.ACCIDENT_TYPE_ID, ac.ACCIDENT_NAME, ac.ACCIDENT_STATUS from accident_infosheet as ac inner join problem as p on p.ID = ac.ID "
				+ conditionSQL
				+ " union "
				+ " select ACCIDENT_WRITE_USER_ID, ACCIDENT_TIME, PROBLEM_TYPE, ac.ID, FACADE_ID, ac.ACCIDENT_TYPE_ID, ac.ACCIDENT_NAME, ac.ACCIDENT_STATUS from accident_report as ac inner join problem as p on p.ID = ac.ID "
				+ conditionSQL
				+ " union "
				+ " select ACCIDENT_WRITE_USER_ID, ACCIDENT_TIME, PROBLEM_TYPE, ac.ID, FACADE_ID, ac.ACCIDENT_TYPE_ID, ac.ACCIDENT_NAME, ac.ACCIDENT_STATUS from accident_checkform2 as ac inner join problem as p on p.ID = ac.ID "
				+ conditionSQL
				+ " union"
				+ " select ACCIDENT_WRITE_USER_ID, ACCIDENT_TIME, PROBLEM_TYPE, ac.ID, FACADE_ID, ac.ACCIDENT_TYPE_ID, ac.ACCIDENT_NAME, ac.ACCIDENT_STATUS from accident_checkform3 as ac inner join problem as p on p.ID = ac.ID "
				+ conditionSQL
				+ ") as fa inner join accident_type as at on at.ID = fa.ACCIDENT_TYPE_ID                                     "
				+ " order by fa.ID desc";

		List<AccidentFacade> facadeList = new LinkedList<AccidentFacade>();
		ProblemDao problemDao = DaoCreator.createProblemDao();
		Connection conn = null;
		PreparedStatement pstm = null;

		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);

			ResultSet rs = pstm.executeQuery();

			Map<Long, AccidentFacade> m = new HashMap<Long, AccidentFacade>();
			while (rs.next()) {
				Long problemID = rs.getLong("ID");
				Long facadeID = rs.getLong("FACADE_ID");
				Problem problem = problemDao.getProblemByID(problemID);
				int problemType = rs.getInt("PROBLEM_TYPE");

				AccidentFacade facade = m.get(facadeID);

				AbstractAccident accident = null;
				AbstractAccident accident1 = null;
				switch (problemType) {
				case Constants.PROBLEM_TYPE_ACCIDENT_REGISTERFORM:
					accident = new AccidentRegisterForm(problem);
					setAccidentField(rs, accident);

					if (facade == null) {
						facade = new AccidentFacade(accident);
						m.put(facadeID, facade);
					}

					facade.setRegisterForm((AccidentRegisterForm) accident);
					break;

				case Constants.PROBLEM_TYPE_ACCIDENT_INFOSHEET:
					accident = new AccidentInfoSheet(problem);
					accident1 = new AccidentQuickReport(problem);
					setAccidentField(rs, accident);
					setAccidentField(rs, accident1);

					if (facade == null) {
						facade = new AccidentFacade(accident);
						m.put(facadeID, facade);
					}

					facade.setInfoSheet((AccidentInfoSheet) accident);
					facade.setQuickReport((AccidentQuickReport) accident1);
					break;

				case Constants.PROBLEM_TYPE_ACCIDENT_REPORT:
					accident = new AccidentReport(problem);
					accident1 = new AccidentCheckForm(problem);
					setAccidentField(rs, accident);
					setAccidentField(rs, accident1);

					if (facade == null) {
						facade = new AccidentFacade(accident);
						m.put(facadeID, facade);
					}

					facade.setReport((AccidentReport) accident);
					facade.setCheckForm1((AccidentCheckForm) accident1);
					break;

				case Constants.PROBLEM_TYPE_ACCIDENT_CHECKFORM2:
					accident = new AccidentCheckForm(problem);
					setAccidentField(rs, accident);

					if (facade == null) {
						facade = new AccidentFacade(accident);
						m.put(facadeID, facade);
					}

					facade.setCheckForm2((AccidentCheckForm) accident);
					break;

				case Constants.PROBLEM_TYPE_ACCIDENT_CHECKFORM3:
					accident = new AccidentCheckForm(problem);
					setAccidentField(rs, accident);

					if (facade == null) {
						facade = new AccidentFacade(accident);
						m.put(facadeID, facade);
					}

					facade.setCheckForm3((AccidentCheckForm) accident);
					break;
				}
			}

			TreeMap<Long, AccidentFacade> tm = new TreeMap<Long, AccidentFacade>(m);
			for (AccidentFacade facade : tm.values()) {
				facadeList.add(0, facade);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return facadeList;
	}

	private void setAccidentField(ResultSet rs, AbstractAccident accident) throws SQLException {
		accident.setAccidentTypeID(rs.getInt("ACCIDENT_TYPE_ID"));
		accident.setAccidentType(rs.getString("ACCIDENT_TYPE"));
		accident.setAccidentName(rs.getString("ACCIDENT_NAME"));
		accident.setAccidentTime(ValueUtil.getCalendar(rs.getObject("ACCIDENT_TIME")));
		accident.setAccidentWriteUserID(rs.getLong("ACCIDENT_WRITE_USER_ID"));
		accident.setAccidentStatus(rs.getInt("ACCIDENT_STATUS"));
		accident.setFacadeID(rs.getLong("FACADE_ID"));
	}

	/**
	 * 
	 * 
	 * @param department1ID
	 * @param equipmentID
	 * @param accidentTypeID
	 * @param problemTitle
	 * @param dateStart
	 * @param dateEnd
	 * @return
	 */
	public List<AccidentFacade> searchAccidents(Long department1ID, Long equipmentID, Long accidentTypeID, String problemTitle,
			String dateStart, String dateEnd) {
		String sql = "select p.ID PROBLEM_ID,  rf.ACCIDENT_TYPE_ID, at.ACCIDENT_TYPE, rf.ACCIDENT_NAME, rf.ACCIDENT_TIME, "
				+ " rf.ACCIDENT_STATUS RF_STATUS,  ais.ACCIDENT_STATUS AIS_STATUS,  aqr.ACCIDENT_STATUS AQR_STATUS, ar.ACCIDENT_STATUS AR_STATUS,  "
				+ " acf1.ACCIDENT_STATUS ACF1_STATUS, acf2.ACCIDENT_STATUS ACF2_STATUS,  acf3.ACCIDENT_STATUS ACF3_STATUS,"
				+ " rf.ACCIDENT_WRITE_USER_ID as RF_WRITE_USER_ID,                "
				+ " ais.ID AIS_ID, ais.ACCIDENT_WRITE_USER_ID AIS_WRITE_UESR_ID,"
				+ " aqr.ID AQR_ID, aqr.ACCIDENT_WRITE_USER_ID AQR_WRITE_USER_ID, "
				+ " ar.ID AR_ID, ar.ACCIDENT_WRITE_USER_ID AR_WRITE_USER_ID,"
				+ " acf1.ID ACF1_ID, acf1.ACCIDENT_WRITE_USER_ID ACF1_WRITE_USER_ID,"
				+ " acf2.ID ACF2_ID, acf2.ACCIDENT_WRITE_USER_ID ACF2_WRITE_USER_ID,"
				+ " acf3.ID ACF3_ID, acf3.ACCIDENT_WRITE_USER_ID ACF3_WRITE_USER_ID "
				+ " from [accident_registerform] as rf                                ";
		sql = sql + " inner join problem as p on p.ID = rf.ID ";
		sql = sql + " inner join accident_type as at on at.ID = rf.ACCIDENT_TYPE_ID ";
		sql = sql + " left outer join accident_infosheet as ais on ais.FACADE_ID = rf.ID ";
		sql = sql + " left outer join accident_quickreport as aqr on aqr.FACADE_ID = rf.ID ";
		sql = sql + " left outer join accident_report as ar on ar.FACADE_ID = rf.ID  ";
		sql = sql + " left outer join accident_checkform1 acf1 on acf1.FACADE_ID = rf.ID ";
		sql = sql + " left outer join accident_checkform2 acf2 on acf2.FACADE_ID = rf.ID ";
		sql = sql + " left outer join accident_checkform3 acf3 on acf3.FACADE_ID = rf.ID                   ";
		sql = sql + " where rf.ACCIDENT_STATUS = " + Constants.ACCIDENT_STATUS_COMPLETE + " ";

		boolean hasPre = true;

		if (department1ID != null && department1ID > 0) {
			if (hasPre) {
				sql += " and ";
			}
			sql += "p.DEPARTMENT1_ID=" + department1ID + " ";
			hasPre = true;
		}

		if (equipmentID != null && equipmentID > 0) {
			if (hasPre) {
				sql += "and ";
			}
			sql += "p.EQUIPMENT_ID=" + equipmentID + " ";
			hasPre = true;
		}

		if (accidentTypeID != null && accidentTypeID > 0) {
			if (hasPre) {
				sql += "and ";
			}
			sql += "rf.ACCIDENT_TYPE_ID=" + accidentTypeID + " ";
			hasPre = true;
		}

		if (problemTitle != null && problemTitle.trim().length() > 0) {
			if (hasPre) {
				sql += "and ";
			}
			sql += "rf.ACCIDENT_NAME like '%" + problemTitle.trim() + "%' ";
			hasPre = true;
		}

		if (dateStart != null && dateStart.trim().length() > 0) {
			if (hasPre) {
				sql += "and ";
			}
			sql += "rf.ACCIDENT_WRITE_TIME>='" + dateStart + " 00:00:00' ";
			hasPre = true;
		}

		if (dateEnd != null && dateEnd.trim().length() > 0) {
			if (hasPre) {
				sql += "and ";
			}
			sql += "rf.ACCIDENT_WRITE_TIME<='" + dateEnd + " 23:59:59' ";
			hasPre = true;
		}

		sql = sql + " order by rf.ACCIDENT_WRITE_TIME desc";

		List<AccidentFacade> facadeList = new ArrayList<AccidentFacade>();
		ProblemDao problemDao = DaoCreator.createProblemDao();
		Connection conn = null;
		PreparedStatement pstm = null;

		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);

			ResultSet rs = pstm.executeQuery();
			while (rs.next()) {
				Long problemID = rs.getLong("PROBLEM_ID");
				Problem problem = problemDao.getProblemByID(problemID);

				AccidentRegisterForm registerForm = new AccidentRegisterForm(problem);
				registerForm.setAccidentTypeID(rs.getInt("ACCIDENT_TYPE_ID"));
				registerForm.setAccidentType(rs.getString("ACCIDENT_TYPE"));
				registerForm.setAccidentName(rs.getString("ACCIDENT_NAME"));
				registerForm.setAccidentTime(ValueUtil.getCalendar(rs.getObject("ACCIDENT_TIME")));
				registerForm.setAccidentWriteUserID(rs.getLong("RF_WRITE_USER_ID"));
				Object obj = rs.getObject("RF_STATUS");
				if (obj != null) {
					registerForm.setAccidentStatus(rs.getInt("RF_STATUS"));
				}

				AccidentInfoSheet infoSheet = new AccidentInfoSheet(problem);
				infoSheet.setId(rs.getLong("AIS_ID"));
				infoSheet.setAccidentWriteUserID(rs.getLong("AIS_WRITE_UESR_ID"));
				obj = rs.getObject("AIS_STATUS");
				if (obj != null) {
					infoSheet.setAccidentStatus(rs.getInt("AIS_STATUS"));
				}

				AccidentQuickReport quickReport = new AccidentQuickReport(problem);
				quickReport.setId(rs.getLong("AQR_ID"));
				quickReport.setAccidentWriteUserID(rs.getLong("AQR_WRITE_USER_ID"));
				obj = rs.getObject("AQR_STATUS");
				if (obj != null) {
					quickReport.setAccidentStatus(rs.getInt("AQR_STATUS"));
				}

				AccidentReport report = new AccidentReport(problem);
				report.setId(rs.getLong("AR_ID"));
				report.setAccidentWriteUserID(rs.getLong("AR_WRITE_USER_ID"));
				obj = rs.getObject("AR_STATUS");
				if (obj != null) {
					report.setAccidentStatus(rs.getInt("AR_STATUS"));
				}

				AccidentCheckForm checkForm1 = new AccidentCheckForm(problem);
				checkForm1.setId(rs.getLong("ACF1_ID"));
				checkForm1.setAccidentWriteUserID(rs.getLong("ACF1_WRITE_USER_ID"));
				obj = rs.getObject("ACF1_STATUS");
				if (obj != null) {
					checkForm1.setAccidentStatus(rs.getInt("ACF1_STATUS"));
				}

				AccidentCheckForm checkForm2 = new AccidentCheckForm(problem);
				checkForm2.setId(rs.getLong("ACF2_ID"));
				checkForm2.setAccidentWriteUserID(rs.getLong("ACF2_WRITE_USER_ID"));
				obj = rs.getObject("ACF2_STATUS");
				if (obj != null) {
					checkForm2.setAccidentStatus(rs.getInt("ACF2_STATUS"));
				}

				AccidentCheckForm checkForm3 = new AccidentCheckForm(problem);
				checkForm3.setId(rs.getLong("ACF3_ID"));
				checkForm3.setAccidentWriteUserID(rs.getLong("ACF3_WRITE_USER_ID"));
				obj = rs.getObject("ACF3_STATUS");
				if (obj != null) {
					checkForm3.setAccidentStatus(rs.getInt("ACF3_STATUS"));
				}

				AccidentFacade facade = new AccidentFacade(registerForm, infoSheet, quickReport, report, checkForm1, checkForm2, checkForm3);

				facadeList.add(facade);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return facadeList;
	}

	public AccidentInfoSheet getInfosheetByID(Long problemID) {
		String sql = "SELECT accident_infosheet.[ID]  ,[ACCIDENT_TYPE_ID]  , [ACCIDENT_TYPE], [ACCIDENT_TIME] ,[ACCIDENT_STATUS]  ,[ACCIDENT_NAME]  ,[LOSS] ,[OVERVIEW] ,[HANDLE] , "
				+ "[REASON] ,[MORAL] ,[CASUALTY] ,[SERIOUS_INJURY_COUNT]  ,[DEATH_COUNT]  ,[ACCIDENT_WRITE_TIME] ,[ACCIDENT_WRITE_USER_ID], [FACADE_ID], [SENDBACK_MSG] "
				+ "FROM [accident_infosheet] "
				+ " left outer join accident_type as at on at.ID = accident_infosheet.ACCIDENT_TYPE_ID where accident_infosheet.ID = ?";
		Connection conn = null;
		PreparedStatement pstm = null;
		AccidentInfoSheet infosheet = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);
			pstm.setLong(1, problemID);

			ResultSet rs = pstm.executeQuery();
			if (rs.next()) {
				ProblemDao problemDao = DaoCreator.createProblemDao();
				Problem problem = problemDao.getProblemByID(problemID);

				infosheet = new AccidentInfoSheet(problem);
				infosheet.setAccidentTypeID(rs.getInt("ACCIDENT_TYPE_ID"));
				infosheet.setAccidentType(rs.getString("ACCIDENT_TYPE"));
				infosheet.setAccidentTime(ValueUtil.getCalendar(rs.getObject("ACCIDENT_TIME")));
				infosheet.setAccidentStatus(rs.getInt("ACCIDENT_STATUS"));
				infosheet.setAccidentName(rs.getString("ACCIDENT_NAME"));
				infosheet.setOverview(rs.getString("OVERVIEW"));
				infosheet.setCasualty(rs.getString("CASUALTY"));
				infosheet.setLoss(rs.getString("LOSS"));
				infosheet.setHandle(rs.getString("HANDLE"));
				infosheet.setReason(rs.getString("REASON"));
				infosheet.setMoral(rs.getString("MORAL"));
				infosheet.setSeriousInjuryCount(rs.getInt("SERIOUS_INJURY_COUNT"));
				infosheet.setDeathCount(rs.getInt("DEATH_COUNT"));

				infosheet.setAccidentWriteTime(ValueUtil.getCalendar(rs.getObject("ACCIDENT_WRITE_TIME")));
				infosheet.setAccidentWriteUserID(rs.getLong("ACCIDENT_WRITE_USER_ID"));
				infosheet.setFacadeID(rs.getLong("FACADE_ID"));
				infosheet.setSendbackMsg(rs.getString("SENDBACK_MSG"));

				List<AccidentCasualty> casualtyList = getCasualtyListByProblemID(problemID);
				infosheet.setCasualtyList(casualtyList);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return infosheet;
	}

	public AccidentQuickReport getQuickReportByID(Long problemID) {
		String sql = "SELECT accident_quickreport.[ID] ,[ACCIDENT_TYPE_ID] ,[ACCIDENT_TYPE] ,[ACCIDENT_STATUS]  ,[ACCIDENT_TIME] ,[ACCIDENT_NAME] ,[LOSS] ,[OVERVIEW] ,"
				+ "[HANDLE]  ,[REASON]  ,[MORAL] ,[CASUALTY] ,[SERIOUS_INJURY_COUNT] ,[DEATH_COUNT] ,[ACCIDENT_WRITE_USER_ID] ,[ACCIDENT_WRITE_TIME], [FACADE_ID], [SENDBACK_MSG]  "
				+ "FROM [accident_quickreport] "
				+ "left outer join accident_type as at on at.ID = accident_quickreport.ACCIDENT_TYPE_ID where accident_quickreport.ID = ?";
		Connection conn = null;
		PreparedStatement pstm = null;
		AccidentQuickReport quickReport = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);
			pstm.setLong(1, problemID);

			ResultSet rs = pstm.executeQuery();
			if (rs.next()) {
				ProblemDao problemDao = DaoCreator.createProblemDao();
				Problem problem = problemDao.getProblemByID(problemID);

				quickReport = new AccidentQuickReport(problem);
				quickReport.setAccidentTypeID(rs.getInt("ACCIDENT_TYPE_ID"));
				quickReport.setAccidentType(rs.getString("ACCIDENT_TYPE"));
				quickReport.setAccidentTime(ValueUtil.getCalendar(rs.getObject("ACCIDENT_TIME")));
				quickReport.setAccidentStatus(rs.getInt("ACCIDENT_STATUS"));
				quickReport.setAccidentName(rs.getString("ACCIDENT_NAME"));
				quickReport.setOverview(rs.getString("OVERVIEW"));
				quickReport.setCasualty(rs.getString("CASUALTY"));
				quickReport.setLoss(rs.getString("LOSS"));
				quickReport.setHandle(rs.getString("HANDLE"));
				quickReport.setReason(rs.getString("REASON"));
				quickReport.setMoral(rs.getString("MORAL"));
				quickReport.setSeriousInjuryCount(rs.getInt("SERIOUS_INJURY_COUNT"));
				quickReport.setDeathCount(rs.getInt("DEATH_COUNT"));

				quickReport.setAccidentWriteTime(ValueUtil.getCalendar(rs.getObject("ACCIDENT_WRITE_TIME")));
				quickReport.setAccidentWriteUserID(rs.getLong("ACCIDENT_WRITE_USER_ID"));
				quickReport.setFacadeID(rs.getLong("FACADE_ID"));
				quickReport.setSendbackMsg(rs.getString("SENDBACK_MSG"));

				List<AccidentCasualty> casualtyList = getCasualtyListByProblemID(problemID);
				quickReport.setCasualtyList(casualtyList);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return quickReport;
	}

	public AccidentQuickReport getQuickReportByFacadeID(Long facadeID) {
		String sql = "SELECT [ID] ,[ACCIDENT_TYPE_ID] ,[ACCIDENT_STATUS]  ,[ACCIDENT_TIME] ,[ACCIDENT_NAME] ,[LOSS] ,[OVERVIEW] ,"
				+ "[HANDLE]  ,[REASON]  ,[MORAL] ,[CASUALTY] ,[SERIOUS_INJURY_COUNT] ,[DEATH_COUNT] ,[ACCIDENT_WRITE_USER_ID] ,[ACCIDENT_WRITE_TIME], [FACADE_ID], [SENDBACK_MSG]  "
				+ "FROM [accident_quickreport] where FACADE_ID = ?";
		Connection conn = null;
		PreparedStatement pstm = null;
		AccidentQuickReport quickReport = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);
			pstm.setLong(1, facadeID);

			ResultSet rs = pstm.executeQuery();
			if (rs.next()) {
				Long problemID = rs.getLong("ID");

				ProblemDao problemDao = DaoCreator.createProblemDao();
				Problem problem = problemDao.getProblemByID(problemID);

				quickReport = new AccidentQuickReport(problem);
				quickReport.setAccidentTypeID(rs.getInt("ACCIDENT_TYPE_ID"));
				quickReport.setAccidentTime(ValueUtil.getCalendar(rs.getObject("ACCIDENT_TIME")));
				quickReport.setAccidentStatus(rs.getInt("ACCIDENT_STATUS"));
				quickReport.setAccidentName(rs.getString("ACCIDENT_NAME"));
				quickReport.setOverview(rs.getString("OVERVIEW"));
				quickReport.setCasualty(rs.getString("CASUALTY"));
				quickReport.setLoss(rs.getString("LOSS"));
				quickReport.setHandle(rs.getString("HANDLE"));
				quickReport.setReason(rs.getString("REASON"));
				quickReport.setMoral(rs.getString("MORAL"));
				quickReport.setSeriousInjuryCount(rs.getInt("SERIOUS_INJURY_COUNT"));
				quickReport.setDeathCount(rs.getInt("DEATH_COUNT"));

				quickReport.setAccidentWriteTime(ValueUtil.getCalendar(rs.getObject("ACCIDENT_WRITE_TIME")));
				quickReport.setAccidentWriteUserID(rs.getLong("ACCIDENT_WRITE_USER_ID"));
				quickReport.setFacadeID(rs.getLong("FACADE_ID"));
				quickReport.setSendbackMsg(rs.getString("SENDBACK_MSG"));

				List<AccidentCasualty> casualtyList = getCasualtyListByProblemID(problemID);
				quickReport.setCasualtyList(casualtyList);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return quickReport;
	}

	public List<AccidentCasualty> getCasualtyListByProblemID(Long problemID) {
		String sql = "SELECT [ID]  ,[ACCIDENT_ID]  ,[NAME] ,[GENDER]  ,[AGE] ,[WORK_AGE] ,[WORK_TYPE] ,[CASUALTY_DETAIL] ,[SERIOUS_LEVEL] "
				+ "FROM [accident_casualty] where ACCIDENT_ID = ?";
		Connection conn = null;
		PreparedStatement pstm = null;
		List<AccidentCasualty> casualtyList = new ArrayList<AccidentCasualty>();
		if (problemID == null) {
			return casualtyList;
		}
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);
			pstm.setLong(1, problemID);

			ResultSet rs = pstm.executeQuery();
			while (rs.next()) {
				AccidentCasualty casualty = new AccidentCasualty();
				casualty.setId(rs.getLong("ID"));
				casualty.setAccidentID(rs.getLong("ACCIDENT_ID"));
				casualty.setName(rs.getString("NAME"));
				casualty.setGender(rs.getInt("GENDER"));
				casualty.setWorkAge(rs.getInt("WORK_AGE"));
				casualty.setAge(rs.getInt("AGE"));
				casualty.setWorkType(rs.getString("WORK_TYPE"));
				casualty.setCasualtyDetail(rs.getString("CASUALTY_DETAIL"));
				casualty.setSeriousLevel(rs.getString("SERIOUS_LEVEL"));

				casualtyList.add(casualty);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return casualtyList;
	}

	public AccidentRegisterForm getRegisterFormByID(Long problemID) {
		String sql = "SELECT accident_registerform.[ID]  ,[ACCIDENT_TYPE_ID] ,at.[ACCIDENT_TYPE], [ACCIDENT_TIME] ,[ACCIDENT_STATUS]  ,[ACCIDENT_NAME] "
				+ " ,[OVERVIEW]  ,[CASUALTY] ,[LOSS] ,[HANDLE] ,[ACCIDENT_WRITE_TIME] ,[ACCIDENT_WRITE_USER_ID], [FACADE_ID] "
				+ "FROM [accident_registerform] "
				+ "left outer join accident_type as at on at.ID =  accident_registerform.ACCIDENT_TYPE_ID "
				+ " where accident_registerform.ID = ?";
		Connection conn = null;
		PreparedStatement pstm = null;
		AccidentRegisterForm registerForm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);
			pstm.setLong(1, problemID);

			ResultSet rs = pstm.executeQuery();
			if (rs.next()) {
				ProblemDao problemDao = DaoCreator.createProblemDao();
				Problem problem = problemDao.getProblemByID(problemID);

				registerForm = new AccidentRegisterForm(problem);
				registerForm.setAccidentTypeID(rs.getInt("ACCIDENT_TYPE_ID"));
				registerForm.setAccidentType(rs.getString("ACCIDENT_TYPE"));
				registerForm.setAccidentTime(ValueUtil.getCalendar(rs.getObject("ACCIDENT_TIME")));
				registerForm.setAccidentStatus(rs.getInt("ACCIDENT_STATUS"));
				registerForm.setAccidentName(rs.getString("ACCIDENT_NAME"));
				registerForm.setOverview(rs.getString("OVERVIEW"));
				registerForm.setCasualty(rs.getString("CASUALTY"));
				registerForm.setLoss(rs.getString("LOSS"));
				registerForm.setHandle(rs.getString("HANDLE"));
				registerForm.setAccidentWriteTime(ValueUtil.getCalendar(rs.getObject("ACCIDENT_WRITE_TIME")));
				registerForm.setAccidentWriteUserID(rs.getLong("ACCIDENT_WRITE_USER_ID"));
				registerForm.setFacadeID(rs.getLong("FACADE_ID"));

			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return registerForm;
	}

	public AccidentRegisterForm getRegisterFormByFacadeID(Long facadeID) {
		String sql = "SELECT [ID]  ,[ACCIDENT_TYPE_ID] ,[ACCIDENT_TIME] ,[ACCIDENT_STATUS]  ,[ACCIDENT_NAME] "
				+ " ,[OVERVIEW]  ,[CASUALTY] ,[LOSS] ,[HANDLE] ,[ACCIDENT_WRITE_TIME] ,[ACCIDENT_WRITE_USER_ID], [FACADE_ID] "
				+ "FROM [accident_registerform] where FACADE_ID = ?";
		Connection conn = null;
		PreparedStatement pstm = null;
		AccidentRegisterForm registerForm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);
			pstm.setLong(1, facadeID);

			ResultSet rs = pstm.executeQuery();
			if (rs.next()) {
				ProblemDao problemDao = DaoCreator.createProblemDao();
				Problem problem = problemDao.getProblemByID(facadeID);

				registerForm = new AccidentRegisterForm(problem);
				registerForm.setAccidentTypeID(rs.getInt("ACCIDENT_TYPE_ID"));
				registerForm.setAccidentTime(ValueUtil.getCalendar(rs.getObject("ACCIDENT_TIME")));
				registerForm.setAccidentStatus(rs.getInt("ACCIDENT_STATUS"));
				registerForm.setAccidentName(rs.getString("ACCIDENT_NAME"));
				registerForm.setOverview(rs.getString("OVERVIEW"));
				registerForm.setCasualty(rs.getString("CASUALTY"));
				registerForm.setLoss(rs.getString("LOSS"));
				registerForm.setHandle(rs.getString("HANDLE"));
				registerForm.setAccidentWriteTime(ValueUtil.getCalendar(rs.getObject("ACCIDENT_WRITE_TIME")));
				registerForm.setAccidentWriteUserID(rs.getLong("ACCIDENT_WRITE_USER_ID"));
				registerForm.setFacadeID(rs.getLong("FACADE_ID"));

			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return registerForm;
	}

	public AccidentReport getReportByID(Long problemID) {
		String sql = "SELECT accident_report.[ID] ,[ACCIDENT_TYPE_ID] ,[ACCIDENT_TYPE] ,[ACCIDENT_TIME] ,[ACCIDENT_STATUS]  ,[CASUALTY] ,[LOSS]  ,"
				+ "[OVERVIEW] ,[REASON]  ,[DUTY] ,[TEAM]  ,[MORAL] ,[INTROSPECTION]  ,[REVIEW_DETAIL] ,[REVIEW_TEAM] ,"
				+ "[REVIEW_TIME] ,[ACCIDENT_WRITE_TIME] ,[ACCIDENT_WRITE_USER_ID] ,[ACCIDENT_NAME], [FACADE_ID], [SENDBACK_MSG] "
				+ "FROM [accident_report] left outer join accident_type as at on at.ID = accident_report.ACCIDENT_TYPE_ID where accident_report.ID = ?";
		Connection conn = null;
		PreparedStatement pstm = null;
		AccidentReport report = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);
			pstm.setLong(1, problemID);

			ResultSet rs = pstm.executeQuery();
			if (rs.next()) {
				ProblemDao problemDao = DaoCreator.createProblemDao();
				Problem problem = problemDao.getProblemByID(problemID);

				report = new AccidentReport(problem);
				report.setAccidentTypeID(rs.getInt("ACCIDENT_TYPE_ID"));
				report.setAccidentType(rs.getString("ACCIDENT_TYPE"));
				report.setAccidentTime(ValueUtil.getCalendar(rs.getObject("ACCIDENT_TIME")));
				report.setAccidentStatus(rs.getInt("ACCIDENT_STATUS"));
				report.setOverview(rs.getString("OVERVIEW"));
				report.setCasualty(rs.getString("CASUALTY"));
				report.setLoss(rs.getString("LOSS"));
				report.setAccidentWriteTime(ValueUtil.getCalendar(rs.getObject("ACCIDENT_WRITE_TIME")));
				report.setAccidentWriteUserID(rs.getLong("ACCIDENT_WRITE_USER_ID"));
				report.setAccidentName(rs.getString("ACCIDENT_NAME"));
				report.setReason(rs.getString("REASON"));
				report.setDuty(rs.getString("DUTY"));
				report.setTeam(rs.getString("TEAM"));
				report.setMoral(rs.getString("MORAL"));
				report.setIntrospection(rs.getString("INTROSPECTION"));
				report.setReviewDetail(rs.getString("REVIEW_DETAIL"));
				report.setReviewTeam(rs.getString("REVIEW_TEAM"));
				report.setReviewTime(ValueUtil.getCalendar(rs.getObject("REVIEW_TIME")));
				report.setFacadeID(rs.getLong("FACADE_ID"));
				report.setSendbackMsg(rs.getString("SENDBACK_MSG"));
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return report;
	}

	public AccidentCheckForm getCheckFormByID(int formType, Long problemID) {
		String sql = "SELECT  acf.[ID] ,[ACCIDENT_TYPE_ID] , [ACCIDENT_TYPE], [ACCIDENT_TIME] ,[ACCIDENT_STATUS] ,[CASUALTY] ,[LOSS] ,[OVERVIEW] ,"
				+ "[REASON] ,[DUTY] ,[TEAM] ,[MORAL] ,[INTROSPECTION] ,[REVIEW_DETAIL] ,[REVIEW_TEAM] ,[REVIEW_TIME] ,"
				+ "[ACCIDENT_WRITE_TIME] ,[ACCIDENT_WRITE_USER_ID] ,[ACCIDENT_NAME], [FACADE_ID], [SENDBACK_MSG]  "
				+ "FROM [accident_checkform"
				+ formType
				+ "] as acf left outer join accident_type as at on at.ID =acf.ACCIDENT_TYPE_ID where acf.ID = ?";
		Connection conn = null;
		PreparedStatement pstm = null;
		AccidentCheckForm checkForm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);
			pstm.setLong(1, problemID);

			ResultSet rs = pstm.executeQuery();
			if (rs.next()) {
				ProblemDao problemDao = DaoCreator.createProblemDao();
				Problem problem = problemDao.getProblemByID(problemID);

				checkForm = new AccidentCheckForm(problem);
				checkForm.setAccidentTypeID(rs.getInt("ACCIDENT_TYPE_ID"));
				checkForm.setAccidentType(rs.getString("ACCIDENT_TYPE"));
				checkForm.setAccidentTime(ValueUtil.getCalendar(rs.getObject("ACCIDENT_TIME")));
				checkForm.setAccidentStatus(rs.getInt("ACCIDENT_STATUS"));
				checkForm.setOverview(rs.getString("OVERVIEW"));
				checkForm.setCasualty(rs.getString("CASUALTY"));
				checkForm.setLoss(rs.getString("LOSS"));
				checkForm.setAccidentWriteTime(ValueUtil.getCalendar(rs.getObject("ACCIDENT_WRITE_TIME")));
				checkForm.setAccidentWriteUserID(rs.getLong("ACCIDENT_WRITE_USER_ID"));
				checkForm.setAccidentName(rs.getString("ACCIDENT_NAME"));
				checkForm.setReason(rs.getString("REASON"));
				checkForm.setDuty(rs.getString("DUTY"));
				checkForm.setTeam(rs.getString("TEAM"));
				checkForm.setMoral(rs.getString("MORAL"));
				checkForm.setIntrospection(rs.getString("INTROSPECTION"));
				checkForm.setReviewDetail(rs.getString("REVIEW_DETAIL"));
				checkForm.setReviewTeam(rs.getString("REVIEW_TEAM"));
				checkForm.setReviewTime(ValueUtil.getCalendar(rs.getObject("REVIEW_TIME")));
				checkForm.setFacadeID(rs.getLong("FACADE_ID"));
				checkForm.setSendbackMsg(rs.getString("SENDBACK_MSG"));
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return checkForm;
	}

	public AccidentCheckForm getCheckFormByFacadeID(int formType, Long facadeID) {
		String sql = "SELECT  [ID] ,[ACCIDENT_TYPE_ID] ,[ACCIDENT_TIME] ,[ACCIDENT_STATUS] ,[CASUALTY] ,[LOSS] ,[OVERVIEW] ,"
				+ "[REASON] ,[DUTY] ,[TEAM] ,[MORAL] ,[INTROSPECTION] ,[REVIEW_DETAIL] ,[REVIEW_TEAM] ,[REVIEW_TIME] ,"
				+ "[ACCIDENT_WRITE_TIME] ,[ACCIDENT_WRITE_USER_ID] ,[ACCIDENT_NAME], [FACADE_ID], [SENDBACK_MSG]  "
				+ "FROM [accident_checkform" + formType + "] where FACADE_ID = ?";
		Connection conn = null;
		PreparedStatement pstm = null;
		AccidentCheckForm checkForm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);
			pstm.setLong(1, facadeID);

			ResultSet rs = pstm.executeQuery();
			if (rs.next()) {
				Long problemID = rs.getLong("ID");

				ProblemDao problemDao = DaoCreator.createProblemDao();
				Problem problem = problemDao.getProblemByID(problemID);

				checkForm = new AccidentCheckForm(problem);
				checkForm.setAccidentTypeID(rs.getInt("ACCIDENT_TYPE_ID"));
				checkForm.setAccidentTime(ValueUtil.getCalendar(rs.getObject("ACCIDENT_TIME")));
				checkForm.setAccidentStatus(rs.getInt("ACCIDENT_STATUS"));
				checkForm.setOverview(rs.getString("OVERVIEW"));
				checkForm.setCasualty(rs.getString("CASUALTY"));
				checkForm.setLoss(rs.getString("LOSS"));
				checkForm.setAccidentWriteTime(ValueUtil.getCalendar(rs.getObject("ACCIDENT_WRITE_TIME")));
				checkForm.setAccidentWriteUserID(rs.getLong("ACCIDENT_WRITE_USER_ID"));
				checkForm.setAccidentName(rs.getString("ACCIDENT_NAME"));
				checkForm.setReason(rs.getString("REASON"));
				checkForm.setDuty(rs.getString("DUTY"));
				checkForm.setTeam(rs.getString("TEAM"));
				checkForm.setMoral(rs.getString("MORAL"));
				checkForm.setIntrospection(rs.getString("INTROSPECTION"));
				checkForm.setReviewDetail(rs.getString("REVIEW_DETAIL"));
				checkForm.setReviewTeam(rs.getString("REVIEW_TEAM"));
				checkForm.setReviewTime(ValueUtil.getCalendar(rs.getObject("REVIEW_TIME")));
				checkForm.setFacadeID(rs.getLong("FACADE_ID"));
				checkForm.setSendbackMsg(rs.getString("SENDBACK_MSG"));
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return checkForm;
	}

	public List<AccidentType> getAccidentTypeList() {
		String sql = "SELECT [ID] ,[ACCIDENT_TYPE] ,[DEPARTMENT1_ID] FROM [accident_type]";
		Connection conn = null;
		PreparedStatement pstm = null;
		List<AccidentType> typeList = new ArrayList<AccidentType>();
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);

			ResultSet rs = pstm.executeQuery();
			while (rs.next()) {
				AccidentType type = new AccidentType();
				type.setId(rs.getLong("ID"));
				type.setAccidentType(rs.getString("ACCIDENT_TYPE"));
				type.setDepartment1ID(rs.getLong("DEPARTMENT1_ID"));

				typeList.add(type);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return typeList;
	}

	public List<AccidentFacade> getApplicationTable(Long department1ID, Long accidentID) {
		String sql = "select p.ID PROBLEM_ID,  rf.ACCIDENT_TYPE_ID, at.ACCIDENT_TYPE, rf.ACCIDENT_NAME, rf.ACCIDENT_TIME, "
				+ " rf.ACCIDENT_STATUS RF_STATUS,  ais.ACCIDENT_STATUS AIS_STATUS,  aqr.ACCIDENT_STATUS AQR_STATUS, ar.ACCIDENT_STATUS AR_STATUS,  "
				+ " acf1.ACCIDENT_STATUS ACF1_STATUS, acf2.ACCIDENT_STATUS ACF2_STATUS,  acf3.ACCIDENT_STATUS ACF3_STATUS,"
				+ " rf.ACCIDENT_WRITE_USER_ID as RF_WRITE_USER_ID,                "
				+ " ais.ID AIS_ID, ais.ACCIDENT_WRITE_USER_ID AIS_WRITE_UESR_ID,"
				+ " aqr.ID AQR_ID, aqr.ACCIDENT_WRITE_USER_ID AQR_WRITE_USER_ID, "
				+ " ar.ID AR_ID, ar.ACCIDENT_WRITE_USER_ID AR_WRITE_USER_ID,"
				+ " acf1.ID ACF1_ID, acf1.ACCIDENT_WRITE_USER_ID ACF1_WRITE_USER_ID,"
				+ " acf2.ID ACF2_ID, acf2.ACCIDENT_WRITE_USER_ID ACF2_WRITE_USER_ID,"
				+ " acf3.ID ACF3_ID, acf3.ACCIDENT_WRITE_USER_ID ACF3_WRITE_USER_ID "
				+ " from [accident_registerform] as rf                                ";
		sql = sql + " inner join problem as p on p.ID = rf.ID ";
		if (department1ID != null) {
			sql = sql + " and p.WRITE_USER_DEPARTMENT1_ID =  " + department1ID + "  ";
		}
		if (accidentID != null) {
			sql = sql + " and p.ID =  " + accidentID + " ";
		}
		sql = sql + " inner join accident_type as at on at.ID = rf.ACCIDENT_TYPE_ID ";
		sql = sql + " left outer join accident_infosheet as ais on ais.FACADE_ID = rf.ID ";
		sql = sql + " left outer join accident_quickreport as aqr on aqr.FACADE_ID = rf.ID ";
		sql = sql + " left outer join accident_report as ar on ar.FACADE_ID = rf.ID  ";
		sql = sql + " left outer join accident_checkform1 acf1 on acf1.FACADE_ID = rf.ID ";
		sql = sql + " left outer join accident_checkform2 acf2 on acf2.FACADE_ID = rf.ID ";
		sql = sql + " left outer join accident_checkform3 acf3 on acf3.FACADE_ID = rf.ID                   ";
		sql = sql + " order by rf.ACCIDENT_WRITE_TIME desc";
		Connection conn = null;
		PreparedStatement pstm = null;
		List<AccidentFacade> facadeList = new ArrayList<AccidentFacade>();
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);

			ProblemDao problemDao = DaoCreator.createProblemDao();
			ResultSet rs = pstm.executeQuery();
			while (rs.next()) {
				Long problemID = rs.getLong("PROBLEM_ID");
				Problem problem = problemDao.getProblemByID(problemID);

				AccidentRegisterForm registerForm = new AccidentRegisterForm(problem);
				registerForm.setAccidentTypeID(rs.getInt("ACCIDENT_TYPE_ID"));
				registerForm.setAccidentType(rs.getString("ACCIDENT_TYPE"));
				registerForm.setAccidentName(rs.getString("ACCIDENT_NAME"));
				registerForm.setAccidentTime(ValueUtil.getCalendar(rs.getObject("ACCIDENT_TIME")));
				registerForm.setAccidentWriteUserID(rs.getLong("RF_WRITE_USER_ID"));
				Object obj = rs.getObject("RF_STATUS");
				if (obj != null) {
					registerForm.setAccidentStatus(rs.getInt("RF_STATUS"));
				}

				AccidentInfoSheet infoSheet = new AccidentInfoSheet(problem);
				infoSheet.setId(rs.getLong("AIS_ID"));
				infoSheet.setAccidentWriteUserID(rs.getLong("AIS_WRITE_UESR_ID"));
				obj = rs.getObject("AIS_STATUS");
				if (obj != null) {
					infoSheet.setAccidentStatus(rs.getInt("AIS_STATUS"));
				}

				AccidentQuickReport quickReport = new AccidentQuickReport(problem);
				quickReport.setId(rs.getLong("AQR_ID"));
				quickReport.setAccidentWriteUserID(rs.getLong("AQR_WRITE_USER_ID"));
				obj = rs.getObject("AQR_STATUS");
				if (obj != null) {
					quickReport.setAccidentStatus(rs.getInt("AQR_STATUS"));
				}

				AccidentReport report = new AccidentReport(problem);
				report.setId(rs.getLong("AR_ID"));
				report.setAccidentWriteUserID(rs.getLong("AR_WRITE_USER_ID"));
				obj = rs.getObject("AR_STATUS");
				if (obj != null) {
					report.setAccidentStatus(rs.getInt("AR_STATUS"));
				}

				AccidentCheckForm checkForm1 = new AccidentCheckForm(problem);
				checkForm1.setId(rs.getLong("ACF1_ID"));
				checkForm1.setAccidentWriteUserID(rs.getLong("ACF1_WRITE_USER_ID"));
				obj = rs.getObject("ACF1_STATUS");
				if (obj != null) {
					checkForm1.setAccidentStatus(rs.getInt("ACF1_STATUS"));
				}

				AccidentCheckForm checkForm2 = new AccidentCheckForm(problem);
				checkForm2.setId(rs.getLong("ACF2_ID"));
				checkForm2.setAccidentWriteUserID(rs.getLong("ACF2_WRITE_USER_ID"));
				obj = rs.getObject("ACF2_STATUS");
				if (obj != null) {
					checkForm2.setAccidentStatus(rs.getInt("ACF2_STATUS"));
				}

				AccidentCheckForm checkForm3 = new AccidentCheckForm(problem);
				checkForm3.setId(rs.getLong("ACF3_ID"));
				checkForm3.setAccidentWriteUserID(rs.getLong("ACF3_WRITE_USER_ID"));
				obj = rs.getObject("ACF3_STATUS");
				if (obj != null) {
					checkForm3.setAccidentStatus(rs.getInt("ACF3_STATUS"));
				}

				AccidentFacade facade = new AccidentFacade(registerForm, infoSheet, quickReport, report, checkForm1, checkForm2, checkForm3);
				facadeList.add(facade);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return facadeList;
	}

	public boolean addAccidentRegisterform(Long problemID, Integer accidentTypeID, Calendar accidentTime, Integer accidentStatus,
			String accidentName, String overview, String casualty, String loss, String handle, Calendar writeTime, Long writeUserID) {
		String sql = "INSERT INTO [accident_registerform]  ([ID] ,[ACCIDENT_TYPE_ID] ,[ACCIDENT_TIME] ,[ACCIDENT_STATUS] ,"
				+ "[ACCIDENT_NAME] ,[OVERVIEW] ,[CASUALTY]  ,[LOSS] ,[HANDLE] ,[ACCIDENT_WRITE_TIME] ,[ACCIDENT_WRITE_USER_ID], [FACADE_ID]) "
				+ "VALUES (?,?,?,?,?,?,?,?,?,?,?,?)";

		Connection conn = null;
		PreparedStatement pstm = null;
		try {

			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);

			conn.setAutoCommit(false);

			pstm.setLong(1, problemID);
			pstm.setInt(2, accidentTypeID);
			pstm.setTimestamp(3, new Timestamp(accidentTime.getTimeInMillis()));
			pstm.setInt(4, accidentStatus);
			pstm.setString(5, accidentName);
			pstm.setString(6, overview);
			if (casualty != null) {
				pstm.setString(7, casualty);
			} else {
				pstm.setNull(7, Types.VARCHAR);
			}
			if (loss != null) {
				pstm.setString(8, loss);
			} else {
				pstm.setNull(8, Types.VARCHAR);
			}
			pstm.setString(9, handle);
			pstm.setTimestamp(10, new Timestamp(writeTime.getTimeInMillis()));
			pstm.setLong(11, writeUserID);
			pstm.setLong(12, problemID);

			pstm.executeUpdate();

			conn.commit();
			conn.setAutoCommit(true);

			return true;
		} catch (Exception e) {
			e.printStackTrace();
			try {
				conn.rollback();
			} catch (Exception e1) {
				e1.printStackTrace();
			}
			return false;
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}
	}

	public boolean updateAccidentRegisterform(Long problemID, Integer accidentTypeID, Calendar accidentTime, Integer accidentStatus,
			String accidentName, String overview, String casualty, String loss, String handle) {
		String sql = "update [accident_registerform]  set [ACCIDENT_TYPE_ID] =? ,[ACCIDENT_TIME] =?  ,[ACCIDENT_STATUS]  =? ,"
				+ "[ACCIDENT_NAME] =?  ,[OVERVIEW] =?  ,[CASUALTY]  =?  ,[LOSS]  =? ,[HANDLE]  =?  " + " where [ID] =? ";

		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);

			conn.setAutoCommit(false);

			pstm.setInt(1, accidentTypeID);
			pstm.setTimestamp(2, new Timestamp(accidentTime.getTimeInMillis()));
			pstm.setInt(3, accidentStatus);
			pstm.setString(4, accidentName);
			pstm.setString(5, overview);
			if (casualty != null) {
				pstm.setString(6, casualty);
			} else {
				pstm.setNull(6, Types.VARCHAR);
			}
			if (loss != null) {
				pstm.setString(7, loss);
			} else {
				pstm.setNull(7, Types.VARCHAR);
			}
			pstm.setString(8, handle);

			pstm.setLong(9, problemID);
			pstm.executeUpdate();

			conn.commit();
			conn.setAutoCommit(true);

			return true;
		} catch (Exception e) {
			e.printStackTrace();
			try {
				conn.rollback();
			} catch (Exception e1) {
				e1.printStackTrace();
			}
			return false;
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}
	}

	public boolean addAccidentInfosheet(Long problemID, Integer accidentTypeID, Calendar accidentTime, Integer accidentStatus,
			String accidentName, String overview, String casualty, String loss, String reason, String moral, Integer seriousInjuryCount,
			Integer deathCount, String handle, Calendar writeTime, Long writeUserID, Long facadeID) {
		String sql = "INSERT INTO [accident_infosheet]  ([ID] ,[ACCIDENT_TYPE_ID] ,[ACCIDENT_TIME] ,[ACCIDENT_STATUS] , "
				+ "[ACCIDENT_NAME],[OVERVIEW],[CASUALTY] ,[LOSS]   ,[HANDLE] ,[ACCIDENT_WRITE_TIME] ,[ACCIDENT_WRITE_USER_ID] "
				+ ",[REASON]  ,[MORAL]  ,[SERIOUS_INJURY_COUNT] ,[DEATH_COUNT], [FACADE_ID]) " + "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

		Connection conn = null;
		PreparedStatement pstm = null;
		try {

			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);

			conn.setAutoCommit(false);

			pstm.setLong(1, problemID);
			pstm.setInt(2, accidentTypeID);
			pstm.setTimestamp(3, new Timestamp(accidentTime.getTimeInMillis()));
			pstm.setInt(4, accidentStatus);
			pstm.setString(5, accidentName);
			pstm.setString(6, overview);
			if (casualty != null) {
				pstm.setString(7, casualty);
			} else {
				pstm.setNull(7, Types.VARCHAR);
			}
			if (loss != null) {
				pstm.setString(8, loss);
			} else {
				pstm.setNull(8, Types.VARCHAR);
			}
			pstm.setString(9, handle);
			pstm.setTimestamp(10, new Timestamp(writeTime.getTimeInMillis()));
			pstm.setLong(11, writeUserID);
			if (reason != null) {
				pstm.setString(12, reason);
			} else {
				pstm.setNull(12, Types.VARCHAR);
			}

			if (moral != null) {
				pstm.setString(13, moral);
			} else {
				pstm.setNull(13, Types.VARCHAR);
			}
			if (seriousInjuryCount != null) {
				pstm.setInt(14, seriousInjuryCount);
			} else {
				pstm.setNull(14, Types.INTEGER);
			}
			if (deathCount != null) {
				pstm.setInt(15, deathCount);
			} else {
				pstm.setNull(15, Types.INTEGER);
			}
			pstm.setLong(16, facadeID);

			pstm.executeUpdate();

			conn.commit();
			conn.setAutoCommit(true);

			return true;
		} catch (Exception e) {
			e.printStackTrace();
			try {
				conn.rollback();
			} catch (Exception e1) {
				e1.printStackTrace();
			}
			return false;
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}
	}

	public boolean updateAccidentInfosheet(Long problemID, Integer accidentTypeID, Calendar accidentTime, Integer accidentStatus,
			String accidentName, String overview, String casualty, String loss, String reason, String moral, Integer seriousInjuryCount,
			Integer deathCount, String handle, String sendbackMsg) {
		String sql = "update [accident_infosheet]  set [ACCIDENT_TYPE_ID] = ? ,[ACCIDENT_TIME] = ? ,[ACCIDENT_STATUS] = ? , "
				+ "[ACCIDENT_NAME] = ?,[OVERVIEW] = ?,[CASUALTY] = ? ,[LOSS] = ?   ,[HANDLE]  = ?, "
				+ "[REASON]  = ? ,[MORAL]  = ? ,[SERIOUS_INJURY_COUNT] = ? , [DEATH_COUNT] = ?, [SENDBACK_MSG] = ? where [ID] = ?";

		Connection conn = null;
		PreparedStatement pstm = null;
		try {

			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);

			conn.setAutoCommit(false);

			pstm.setInt(1, accidentTypeID);
			pstm.setTimestamp(2, new Timestamp(accidentTime.getTimeInMillis()));
			pstm.setInt(3, accidentStatus);
			pstm.setString(4, accidentName);
			pstm.setString(5, overview);
			if (casualty != null) {
				pstm.setString(6, casualty);
			} else {
				pstm.setNull(6, Types.VARCHAR);
			}
			if (loss != null) {
				pstm.setString(7, loss);
			} else {
				pstm.setNull(7, Types.VARCHAR);
			}
			pstm.setString(8, handle);

			if (reason != null) {
				pstm.setString(9, reason);
			} else {
				pstm.setNull(9, Types.VARCHAR);
			}

			if (moral != null) {
				pstm.setString(10, moral);
			} else {
				pstm.setNull(10, Types.VARCHAR);
			}
			if (seriousInjuryCount != null) {
				pstm.setInt(11, seriousInjuryCount);
			} else {
				pstm.setNull(11, Types.INTEGER);
			}
			if (deathCount != null) {
				pstm.setInt(12, deathCount);
			} else {
				pstm.setNull(12, Types.INTEGER);
			}
			if (sendbackMsg != null) {
				pstm.setString(13, sendbackMsg);
			} else {
				pstm.setNull(13, Types.VARCHAR);
			}

			pstm.setLong(14, problemID);
			pstm.executeUpdate();

			conn.commit();
			conn.setAutoCommit(true);

			return true;
		} catch (Exception e) {
			e.printStackTrace();
			try {
				conn.rollback();
			} catch (Exception e1) {
				e1.printStackTrace();
			}
			return false;
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}
	}

	public boolean addAccidentQuickreport(Long problemID, Integer accidentTypeID, Calendar accidentTime, Integer accidentStatus,
			String overview, String casualty, String loss, String reason, String moral, Integer seriousInjuryCount, Integer deathCount,
			String handle, Calendar writeTime, Long writeUserID, String accidentName, Long facadeID) {
		String sql = "INSERT INTO [accident_quickreport]  ([ID] ,[ACCIDENT_TYPE_ID] ,[ACCIDENT_TIME]  ,[ACCIDENT_STATUS] ,[LOSS] "
				+ " ,[OVERVIEW] ,[HANDLE] ,[REASON] ,[MORAL] ,[CASUALTY] ,[SERIOUS_INJURY_COUNT] ,[DEATH_COUNT],[ACCIDENT_WRITE_TIME] , "
				+ "[ACCIDENT_WRITE_USER_ID], [ACCIDENT_NAME], [FACADE_ID]  ) " + "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

		Connection conn = null;
		PreparedStatement pstm = null;
		try {

			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);

			conn.setAutoCommit(false);

			pstm.setLong(1, problemID);
			pstm.setInt(2, accidentTypeID);
			pstm.setTimestamp(3, new Timestamp(accidentTime.getTimeInMillis()));
			pstm.setInt(4, accidentStatus);
			if (loss != null) {
				pstm.setString(5, loss);
			} else {
				pstm.setNull(5, Types.VARCHAR);
			}
			pstm.setString(6, overview);
			if (handle != null) {
				pstm.setString(7, handle);
			} else {
				pstm.setNull(7, Types.VARCHAR);
			}

			if (reason != null) {
				pstm.setString(8, reason);
			} else {
				pstm.setNull(8, Types.VARCHAR);
			}
			if (moral != null) {
				pstm.setString(9, moral);
			} else {
				pstm.setNull(9, Types.VARCHAR);
			}
			if (casualty != null) {
				pstm.setString(10, casualty);
			} else {
				pstm.setNull(10, Types.VARCHAR);
			}
			if (seriousInjuryCount != null) {
				pstm.setInt(11, seriousInjuryCount);
			} else {
				pstm.setNull(11, Types.INTEGER);
			}
			if (deathCount != null) {
				pstm.setInt(12, deathCount);
			} else {
				pstm.setNull(12, Types.INTEGER);
			}

			pstm.setTimestamp(13, new Timestamp(writeTime.getTimeInMillis()));
			pstm.setLong(14, writeUserID);

			pstm.setString(15, accidentName);
			pstm.setLong(16, facadeID);

			pstm.executeUpdate();

			conn.commit();
			conn.setAutoCommit(true);

			return true;
		} catch (Exception e) {
			e.printStackTrace();
			try {
				conn.rollback();
			} catch (Exception e1) {
				e1.printStackTrace();
			}
			return false;
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}
	}

	public boolean updateAccidentQuickreport(Long problemID, Integer accidentTypeID, Calendar accidentTime, Integer accidentStatus,
			String overview, String casualty, String loss, String reason, String moral, Integer seriousInjuryCount, Integer deathCount,
			String handle, String accidentName, String sendbackMsg) {
		String sql = "update [accident_quickreport]  set [ACCIDENT_TYPE_ID]  = ?,[ACCIDENT_TIME]  = ? ,[ACCIDENT_STATUS] = ? ,[LOSS] = ? "
				+ " ,[OVERVIEW] = ? ,[HANDLE] = ? ,[REASON] = ? ,[MORAL]  = ?,[CASUALTY]  = ?,[SERIOUS_INJURY_COUNT]  = ?,[DEATH_COUNT] = ?, [ACCIDENT_NAME]  = ?, [SENDBACK_MSG] = ? "
				+ "where [ID] = ?";

		Connection conn = null;
		PreparedStatement pstm = null;
		try {

			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);

			conn.setAutoCommit(false);

			pstm.setInt(1, accidentTypeID);
			pstm.setTimestamp(2, new Timestamp(accidentTime.getTimeInMillis()));
			pstm.setInt(3, accidentStatus);
			if (loss != null) {
				pstm.setString(4, loss);
			} else {
				pstm.setNull(4, Types.VARCHAR);
			}
			pstm.setString(5, overview);
			if (handle != null) {
				pstm.setString(6, handle);
			} else {
				pstm.setNull(6, Types.VARCHAR);
			}

			if (reason != null) {
				pstm.setString(7, reason);
			} else {
				pstm.setNull(7, Types.VARCHAR);
			}
			if (moral != null) {
				pstm.setString(8, moral);
			} else {
				pstm.setNull(8, Types.VARCHAR);
			}
			if (casualty != null) {
				pstm.setString(9, casualty);
			} else {
				pstm.setNull(9, Types.VARCHAR);
			}
			if (seriousInjuryCount != null) {
				pstm.setInt(10, seriousInjuryCount);
			} else {
				pstm.setNull(10, Types.INTEGER);
			}
			if (deathCount != null) {
				pstm.setInt(11, deathCount);
			} else {
				pstm.setNull(11, Types.INTEGER);
			}

			pstm.setString(12, accidentName);

			if (sendbackMsg != null) {
				pstm.setString(13, sendbackMsg);
			} else {
				pstm.setNull(13, Types.VARCHAR);
			}
			pstm.setLong(14, problemID);
			pstm.executeUpdate();

			conn.commit();
			conn.setAutoCommit(true);

			return true;
		} catch (Exception e) {
			e.printStackTrace();
			try {
				conn.rollback();
			} catch (Exception e1) {
				e1.printStackTrace();
			}
			return false;
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}
	}

	public boolean addAccidentReport(Long problemID, Integer accidentTypeID, Calendar accidentTime, Integer accidentStatus,
			String overview, String casualty, String loss, String reason, String moral, Calendar writeTime, Long writeUserID, String duty,
			String team, String introspection, String reviewDetail, String reviewTeam, Calendar reviewTime, String accidentName,
			Long facadeID) {
		String sql = "INSERT INTO [accident_report]  ([ID] ,[ACCIDENT_TYPE_ID] ,[ACCIDENT_TIME] ,[ACCIDENT_STATUS] ,[LOSS], [OVERVIEW] , "
				+ "[CASUALTY],[REASON] ,[MORAL] ,[ACCIDENT_WRITE_TIME] ,[ACCIDENT_WRITE_USER_ID] ,[DUTY]  ,[TEAM] ,[INTROSPECTION] ,[REVIEW_DETAIL] ,"
				+ "[REVIEW_TEAM] ,[REVIEW_TIME] ,[ACCIDENT_NAME], [FACADE_ID]) " + "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

		Connection conn = null;
		PreparedStatement pstm = null;
		try {

			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);

			conn.setAutoCommit(false);

			pstm.setLong(1, problemID);
			pstm.setInt(2, accidentTypeID);
			pstm.setTimestamp(3, new Timestamp(accidentTime.getTimeInMillis()));
			pstm.setInt(4, accidentStatus);
			if (loss != null) {
				pstm.setString(5, loss);
			} else {
				pstm.setNull(5, Types.VARCHAR);
			}
			pstm.setString(6, overview);

			if (casualty != null) {
				pstm.setString(7, casualty);
			} else {
				pstm.setNull(7, Types.VARCHAR);
			}

			if (reason != null) {
				pstm.setString(8, reason);
			} else {
				pstm.setNull(8, Types.VARCHAR);
			}

			if (moral != null) {
				pstm.setString(9, moral);
			} else {
				pstm.setNull(9, Types.VARCHAR);
			}

			pstm.setTimestamp(10, new Timestamp(writeTime.getTimeInMillis()));
			pstm.setLong(11, writeUserID);

			if (duty != null) {
				pstm.setString(12, duty);
			} else {
				pstm.setNull(12, Types.VARCHAR);
			}
			if (team != null) {
				pstm.setString(13, team);
			} else {
				pstm.setNull(13, Types.VARCHAR);
			}
			if (introspection != null) {
				pstm.setString(14, introspection);
			} else {
				pstm.setNull(14, Types.VARCHAR);
			}
			if (reviewDetail != null) {
				pstm.setString(15, reviewDetail);
			} else {
				pstm.setNull(15, Types.VARCHAR);
			}
			if (reviewTeam != null) {
				pstm.setString(16, reviewTeam);
			} else {
				pstm.setNull(16, Types.VARCHAR);
			}
			if (reviewTime != null) {
				pstm.setTimestamp(17, new Timestamp(reviewTime.getTimeInMillis()));
			} else {
				pstm.setNull(17, Types.TIMESTAMP);
			}
			if (accidentName != null) {
				pstm.setString(18, accidentName);
			} else {
				pstm.setNull(18, Types.VARCHAR);
			}
			pstm.setLong(19, facadeID);

			pstm.executeUpdate();

			conn.commit();
			conn.setAutoCommit(true);

			return true;
		} catch (Exception e) {
			e.printStackTrace();
			try {
				conn.rollback();
			} catch (Exception e1) {
				e1.printStackTrace();
			}
			return false;
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}
	}

	public boolean updateAccidentReport(Long problemID, Integer accidentTypeID, Calendar accidentTime, Integer accidentStatus,
			String overview, String casualty, String loss, String reason, String moral, String duty, String team, String introspection,
			String reviewDetail, String reviewTeam, Calendar reviewTime, String accidentName, String sendbackMsg) {
		String sql = "update [accident_report]  set [ACCIDENT_TYPE_ID]=? ,[ACCIDENT_TIME]=? ,[ACCIDENT_STATUS]=? ,[LOSS]=?, [OVERVIEW]=? , "
				+ "[CASUALTY]=?,[REASON]=? ,[MORAL]=? ,[DUTY]=?  ,[TEAM]=? ,[INTROSPECTION]=? ,[REVIEW_DETAIL]=? ,"
				+ "[REVIEW_TEAM]=? ,[REVIEW_TIME]=? ,[ACCIDENT_NAME]=?, [SENDBACK_MSG] = ? where [ID]=?";

		Connection conn = null;
		PreparedStatement pstm = null;
		try {

			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);

			conn.setAutoCommit(false);

			pstm.setInt(1, accidentTypeID);
			pstm.setTimestamp(2, new Timestamp(accidentTime.getTimeInMillis()));
			pstm.setInt(3, accidentStatus);
			if (loss != null) {
				pstm.setString(4, loss);
			} else {
				pstm.setNull(4, Types.VARCHAR);
			}
			pstm.setString(5, overview);

			if (casualty != null) {
				pstm.setString(6, casualty);
			} else {
				pstm.setNull(6, Types.VARCHAR);
			}

			if (reason != null) {
				pstm.setString(7, reason);
			} else {
				pstm.setNull(7, Types.VARCHAR);
			}

			if (moral != null) {
				pstm.setString(8, moral);
			} else {
				pstm.setNull(8, Types.VARCHAR);
			}

			if (duty != null) {
				pstm.setString(9, duty);
			} else {
				pstm.setNull(9, Types.VARCHAR);
			}
			if (team != null) {
				pstm.setString(10, team);
			} else {
				pstm.setNull(10, Types.VARCHAR);
			}
			if (introspection != null) {
				pstm.setString(11, introspection);
			} else {
				pstm.setNull(11, Types.VARCHAR);
			}
			if (reviewDetail != null) {
				pstm.setString(12, reviewDetail);
			} else {
				pstm.setNull(12, Types.VARCHAR);
			}
			if (reviewTeam != null) {
				pstm.setString(13, reviewTeam);
			} else {
				pstm.setNull(13, Types.VARCHAR);
			}
			if (reviewTime != null) {
				pstm.setTimestamp(14, new Timestamp(reviewTime.getTimeInMillis()));
			} else {
				pstm.setNull(14, Types.TIMESTAMP);
			}
			if (accidentName != null) {
				pstm.setString(15, accidentName);
			} else {
				pstm.setNull(15, Types.VARCHAR);
			}

			if (sendbackMsg != null) {
				pstm.setString(16, sendbackMsg);
			} else {
				pstm.setNull(16, Types.VARCHAR);
			}
			pstm.setLong(17, problemID);
			pstm.executeUpdate();

			conn.commit();
			conn.setAutoCommit(true);

			return true;
		} catch (Exception e) {
			e.printStackTrace();
			try {
				conn.rollback();
			} catch (Exception e1) {
				e1.printStackTrace();
			}
			return false;
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}
	}

	public boolean addAccidentCheckform(int formID, Long problemID, Integer accidentTypeID, Calendar accidentTime, Integer accidentStatus,
			String overview, String casualty, String loss, String reason, String moral, Calendar writeTime, Long writeUserID, String duty,
			String team, String introspection, String reviewDetail, String reviewTeam, Calendar reviewTime, String accidentName,
			Long facadeID) {
		String sql = "INSERT INTO [accident_checkform"
				+ formID
				+ "]  ([ID] ,[ACCIDENT_TYPE_ID] ,[ACCIDENT_TIME] ,[ACCIDENT_STATUS] ,[LOSS], [OVERVIEW] , "
				+ "[CASUALTY],[REASON] ,[MORAL] ,[ACCIDENT_WRITE_TIME] ,[ACCIDENT_WRITE_USER_ID] ,[DUTY]  ,[TEAM] ,[INTROSPECTION] ,[REVIEW_DETAIL] ,"
				+ "[REVIEW_TEAM] ,[REVIEW_TIME] ,[ACCIDENT_NAME], [FACADE_ID]) " + "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

		Connection conn = null;
		PreparedStatement pstm = null;
		try {

			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);

			conn.setAutoCommit(false);

			pstm.setLong(1, problemID);
			pstm.setInt(2, accidentTypeID);
			pstm.setTimestamp(3, new Timestamp(accidentTime.getTimeInMillis()));
			pstm.setInt(4, accidentStatus);
			if (loss != null) {
				pstm.setString(5, loss);
			} else {
				pstm.setNull(5, Types.VARCHAR);
			}
			if (overview != null) {
				pstm.setString(6, overview);
			} else {
				pstm.setNull(6, Types.VARCHAR);
			}

			if (casualty != null) {
				pstm.setString(7, casualty);
			} else {
				pstm.setNull(7, Types.VARCHAR);
			}

			if (reason != null) {
				pstm.setString(8, reason);
			} else {
				pstm.setNull(8, Types.VARCHAR);
			}

			if (moral != null) {
				pstm.setString(9, moral);
			} else {
				pstm.setNull(9, Types.VARCHAR);
			}

			pstm.setTimestamp(10, new Timestamp(writeTime.getTimeInMillis()));
			pstm.setLong(11, writeUserID);

			if (duty != null) {
				pstm.setString(12, duty);
			} else {
				pstm.setNull(12, Types.VARCHAR);
			}
			if (team != null) {
				pstm.setString(13, team);
			} else {
				pstm.setNull(13, Types.VARCHAR);
			}
			if (introspection != null) {
				pstm.setString(14, introspection);
			} else {
				pstm.setNull(14, Types.VARCHAR);
			}
			if (reviewDetail != null) {
				pstm.setString(15, reviewDetail);
			} else {
				pstm.setNull(15, Types.VARCHAR);
			}
			if (reviewTeam != null) {
				pstm.setString(16, reviewTeam);
			} else {
				pstm.setNull(16, Types.VARCHAR);
			}
			if (reviewTime != null) {
				pstm.setTimestamp(17, new Timestamp(reviewTime.getTimeInMillis()));
			} else {
				pstm.setNull(17, Types.TIMESTAMP);
			}
			if (accidentName != null) {
				pstm.setString(18, accidentName);
			} else {
				pstm.setNull(18, Types.VARCHAR);
			}
			pstm.setLong(19, facadeID);

			pstm.executeUpdate();

			conn.commit();
			conn.setAutoCommit(true);

			return true;
		} catch (Exception e) {
			e.printStackTrace();
			try {
				conn.rollback();
			} catch (Exception e1) {
				e1.printStackTrace();
			}
			return false;
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}
	}

	public boolean updateAccidentCheckform(int formID, Long problemID, Integer accidentTypeID, Calendar accidentTime,
			Integer accidentStatus, String overview, String casualty, String loss, String reason, String moral, String duty, String team,
			String introspection, String reviewDetail, String reviewTeam, Calendar reviewTime, String accidentName, String sendbackMsg) {
		String sql = "update [accident_checkform" + formID
				+ "]  set [ACCIDENT_TYPE_ID]=? ,[ACCIDENT_TIME]=? ,[ACCIDENT_STATUS]=? ,[LOSS]=?, [OVERVIEW]=? , "
				+ "[CASUALTY]=?,[REASON]=? ,[MORAL]=? ,[DUTY]=?  ,[TEAM]=? ,[INTROSPECTION]=? ,[REVIEW_DETAIL]=? ,"
				+ "[REVIEW_TEAM]=? ,[REVIEW_TIME] =?,[ACCIDENT_NAME]=?, [SENDBACK_MSG] = ? where [ID]=?";

		Connection conn = null;
		PreparedStatement pstm = null;
		try {

			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);

			conn.setAutoCommit(false);

			pstm.setInt(1, accidentTypeID);
			pstm.setTimestamp(2, new Timestamp(accidentTime.getTimeInMillis()));
			pstm.setInt(3, accidentStatus);
			if (loss != null) {
				pstm.setString(4, loss);
			} else {
				pstm.setNull(4, Types.VARCHAR);
			}
			if (overview != null) {
				pstm.setString(5, overview);
			} else {
				pstm.setNull(5, Types.VARCHAR);
			}

			if (casualty != null) {
				pstm.setString(6, casualty);
			} else {
				pstm.setNull(6, Types.VARCHAR);
			}

			if (reason != null) {
				pstm.setString(7, reason);
			} else {
				pstm.setNull(7, Types.VARCHAR);
			}

			if (moral != null) {
				pstm.setString(8, moral);
			} else {
				pstm.setNull(8, Types.VARCHAR);
			}

			if (duty != null) {
				pstm.setString(9, duty);
			} else {
				pstm.setNull(9, Types.VARCHAR);
			}
			if (team != null) {
				pstm.setString(10, team);
			} else {
				pstm.setNull(10, Types.VARCHAR);
			}
			if (introspection != null) {
				pstm.setString(11, introspection);
			} else {
				pstm.setNull(11, Types.VARCHAR);
			}
			if (reviewDetail != null) {
				pstm.setString(12, reviewDetail);
			} else {
				pstm.setNull(12, Types.VARCHAR);
			}
			if (reviewTeam != null) {
				pstm.setString(13, reviewTeam);
			} else {
				pstm.setNull(13, Types.VARCHAR);
			}
			if (reviewTime != null) {
				pstm.setTimestamp(14, new Timestamp(reviewTime.getTimeInMillis()));
			} else {
				pstm.setNull(14, Types.TIMESTAMP);
			}
			if (accidentName != null) {
				pstm.setString(15, accidentName);
			} else {
				pstm.setNull(15, Types.VARCHAR);
			}

			if (sendbackMsg != null) {
				pstm.setString(16, sendbackMsg);
			} else {
				pstm.setNull(16, Types.VARCHAR);
			}
			pstm.setLong(17, problemID);
			pstm.executeUpdate();

			conn.commit();
			conn.setAutoCommit(true);

			return true;
		} catch (Exception e) {
			e.printStackTrace();
			try {
				conn.rollback();
			} catch (Exception e1) {
				e1.printStackTrace();
			}
			return false;
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}
	}

	public boolean deleteAccidentInfosheet(Long[] problemIDs) {
		if (problemIDs == null || problemIDs.length == 0) {
			return true;
		}
		String sql = "delete from accident_infosheet where ID in " + Util.generateInCluse(problemIDs, false);

		Connection conn = null;
		PreparedStatement pstm = null;
		try {

			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);

			conn.setAutoCommit(false);

			pstm.executeUpdate();

			conn.commit();
			conn.setAutoCommit(true);

			return true;
		} catch (Exception e) {
			e.printStackTrace();
			try {
				conn.rollback();
			} catch (Exception e1) {
				e1.printStackTrace();
			}
			return false;
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}
	}

	public boolean deleteAccidentCheckform(int formID, Long[] problemIDs) {
		if (problemIDs == null || problemIDs.length == 0) {
			return true;
		}
		String sql = "delete from accident_checkform" + formID + " where ID in " + Util.generateInCluse(problemIDs, false);

		Connection conn = null;
		PreparedStatement pstm = null;
		try {

			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);

			conn.setAutoCommit(false);

			pstm.executeUpdate();

			conn.commit();
			conn.setAutoCommit(true);

			return true;
		} catch (Exception e) {
			e.printStackTrace();
			try {
				conn.rollback();
			} catch (Exception e1) {
				e1.printStackTrace();
			}
			return false;
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}
	}

	public boolean deleteAccidentReport(Long[] problemIDs) {
		if (problemIDs == null || problemIDs.length == 0) {
			return true;
		}
		String sql = "delete from accident_report where ID in " + Util.generateInCluse(problemIDs, false);

		Connection conn = null;
		PreparedStatement pstm = null;
		try {

			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);

			conn.setAutoCommit(false);

			pstm.executeUpdate();

			conn.commit();
			conn.setAutoCommit(true);

			return true;
		} catch (Exception e) {
			e.printStackTrace();
			try {
				conn.rollback();
			} catch (Exception e1) {
				e1.printStackTrace();
			}
			return false;
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}
	}

	public boolean deleteAccidentQuickReport(Long[] problemIDs) {
		if (problemIDs == null || problemIDs.length == 0) {
			return true;
		}
		String sql = "delete from accident_quickreport where ID in " + Util.generateInCluse(problemIDs, false);

		Connection conn = null;
		PreparedStatement pstm = null;
		try {

			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);

			conn.setAutoCommit(false);

			pstm.executeUpdate();

			conn.commit();
			conn.setAutoCommit(true);

			return true;
		} catch (Exception e) {
			e.printStackTrace();
			try {
				conn.rollback();
			} catch (Exception e1) {
				e1.printStackTrace();
			}
			return false;
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}
	}

	public boolean deleteAccidentCasualtyByProblemID(Long[] problemIDs) {
		if (problemIDs == null || problemIDs.length == 0) {
			return true;
		}
		String sql = "delete from accident_casualty where ACCIDENT_ID in " + Util.generateInCluse(problemIDs, false);

		Connection conn = null;
		PreparedStatement pstm = null;
		try {

			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);

			conn.setAutoCommit(false);

			pstm.executeUpdate();

			conn.commit();
			conn.setAutoCommit(true);

			return true;
		} catch (Exception e) {
			e.printStackTrace();
			try {
				conn.rollback();
			} catch (Exception e1) {
				e1.printStackTrace();
			}
			return false;
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}
	}

	private String getParentheseCaluse(Long problemID, AccidentCasualty casualty) {
		StringBuffer sb = new StringBuffer();
		sb.append("(");
		sb.append(problemID + ",");

		if (casualty.getName() == null) {
			sb.append(" NULL,");
		} else {
			sb.append("'" + casualty.getName() + "',");
		}

		if (casualty.getGender() == null) {
			sb.append(" NULL,");
		} else {
			sb.append(casualty.getGender() + ",");
		}

		if (casualty.getAge() == null) {
			sb.append(" NULL,");
		} else {
			sb.append(casualty.getAge() + ",");
		}
		if (casualty.getWorkAge() == null) {
			sb.append(" NULL,");
		} else {
			sb.append(casualty.getWorkAge() + ",");
		}

		if (casualty.getWorkType() == null) {
			sb.append(" NULL,");
		} else {
			sb.append("'" + casualty.getWorkType() + "',");
		}

		if (casualty.getCasualtyDetail() == null) {
			sb.append(" NULL,");
		} else {
			sb.append("'" + casualty.getCasualtyDetail() + "',");
		}
		if (casualty.getSeriousLevel() == null) {
			sb.append(" NULL");
		} else {
			sb.append("'" + casualty.getSeriousLevel() + "'");
		}
		sb.append(")");

		return sb.toString();
	}

	public boolean addAccidentCasualty(Long problemID, List<AccidentCasualty> accidentCasualtyList) {
		if (accidentCasualtyList == null || accidentCasualtyList.size() == 0) {
			return true;
		}
		StringBuffer sb = new StringBuffer();
		sb.append("INSERT INTO [accident_casualty]  ([ACCIDENT_ID] ,[NAME] ,[GENDER] ,[AGE] ,[WORK_AGE] ,[WORK_TYPE] ,[CASUALTY_DETAIL] ,[SERIOUS_LEVEL]) ");
		sb.append("VALUES ");
		for (int i = 0; i < accidentCasualtyList.size() - 1; i++) {
			AccidentCasualty casualty = accidentCasualtyList.get(i);
			sb.append(getParentheseCaluse(problemID, casualty));
			sb.append(",");
		}
		sb.append(getParentheseCaluse(problemID, accidentCasualtyList.get(accidentCasualtyList.size() - 1)));

		String sql = sb.toString();
		Connection conn = null;
		PreparedStatement pstm = null;
		try {

			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);

			conn.setAutoCommit(false);
			pstm.executeUpdate();

			conn.commit();
			conn.setAutoCommit(true);

			return true;
		} catch (Exception e) {
			e.printStackTrace();
			try {
				conn.rollback();
			} catch (Exception e1) {
				e1.printStackTrace();
			}
			return false;
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}
	}
}
